package com.apes.fico.finance.respository;

import com.apes.fico.finance.model.gl.GLVoucherItem;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface GLVoucherItemRepository extends CustomRepository<GLVoucherItem, String> {

    @Query(value = " select b.cash_flow_id as code, sum(b.debit - b.credit) as money " +
            "  from gl_voucher a, gl_voucher_item b " +
            " where a.id = b.voucher_id " +
            "   and b.cash_flow_id is not null " +
            "   and (a.month between :month and :stopMonth) " +
            "   and a.year = :year " +
            "   and a.company_id = :company " +
            " group by b.cash_flow_id ", nativeQuery = true)
    List<Map<String, Double>> findAllByCompanyAndMonthAndYear(@Param("company") String company, @Param("month") int month,
                                                              @Param("stopMonth") int stopMonth, @Param("year") int year);

    @Query(value = " SELECT a.code, sum(b.credit - b.debit) as money " +
            " FROM  gl_voucher_aux a, gl_voucher_item b, gl_voucher e " +
            " WHERE a.voucher_item_id = b.id " +
            " AND (e.month between :month and :stopMonth) " +
            " AND e.year = :year " +
            " AND e.company_id = :company " +
            " AND e.id = b.voucher_id " +
            " AND a.code = '6603090000' " +
            " GROUP BY a.code ", nativeQuery = true)
    List<Map<String, Double>> findCashByCompanyAndMonthAndYear(@Param("company") String company, @Param("month") int month,
                                                               @Param("stopMonth") int stopMonth, @Param("year") int year);

}
